using OrderSystem.Common;
using OrderSystem.SystemFramework;
using System;
using System.Data;
using System.Data.SqlClient;

namespace OrderSystem.DataAccess
{
	public class Stuffs
	{
		private SqlDataAdapter dataAdapter;

		private SqlCommand selectCommand;

		private SqlCommand insertCommand;

		private SqlCommand updateCommand;

		private SqlCommand deleteCommand;

		public DataTable LoadStuff(int nPKID)
		{
			DataTable dataTable = new DataTable("Stuff");
			string cmdText = "SELECT s.PKID AS 序号,s.[Type] AS 类型,s.Length as 长,s.Width AS 宽,s.Quantity AS 数量,s.[Date] AS 日期,s.Remark AS 备注 from [Stuff] as s where s.PKID=@PKID";
			try
			{
				this.selectCommand = new SqlCommand(cmdText, new SqlConnection(AppSettings.ConnectionString));
				this.selectCommand.Connection.Open();
				this.selectCommand.Parameters.Add(new SqlParameter("@PKID", SqlDbType.Int));
				this.selectCommand.Parameters["@PKID"].Value = nPKID;
				this.dataAdapter = new SqlDataAdapter(this.selectCommand);
				this.dataAdapter.Fill(dataTable);
			}
			catch (SqlException ex)
			{
				throw ex;
			}
			return dataTable;
		}

		public DataTable GetStuffsByCondition(StuffCondition sc)
		{
			this.selectCommand = new SqlCommand();
			string text = "";
			if (sc.Type != null)
			{
				if (text == "")
				{
					text += "s.[Type] LIKE '%'+@Type+'%'";
				}
				else
				{
					text += " AND s.[Type] LIKE '%'+@Type+'%'";
				}
				this.selectCommand.Parameters.Add(new SqlParameter("@Type", SqlDbType.NVarChar, 32));
				this.selectCommand.Parameters["@Type"].Value = sc.Type.Replace(" ", "%");
			}
			if (sc.Length > 0)
			{
				if (text == "")
				{
					text += "s.Length =@Length";
				}
				else
				{
					text += " AND s.Length =@Length";
				}
				this.selectCommand.Parameters.Add(new SqlParameter("@Length", SqlDbType.Int));
				this.selectCommand.Parameters["@Length"].Value = sc.Length;
			}
			if (sc.Width > 0)
			{
				if (text == "")
				{
					text += "s.Width =@Width";
				}
				else
				{
					text += " AND s.Width =@Width";
				}
				this.selectCommand.Parameters.Add(new SqlParameter("@Width", SqlDbType.Int));
				this.selectCommand.Parameters["@Width"].Value = sc.Width;
			}
			if (sc.Quantity > 0)
			{
				if (text == "")
				{
					text += "s.Quantity =@Quantity";
				}
				else
				{
					text += " AND s.Quantity =@Quantity";
				}
				this.selectCommand.Parameters.Add(new SqlParameter("@Quantity", SqlDbType.Int));
				this.selectCommand.Parameters["@Quantity"].Value = sc.Quantity;
			}
			if (sc.DateStart != null)
			{
				if (text == "")
				{
					text += "s.[Date]>=CONVERT(DATETIME,@DateStart) AND s.[Date]<=CONVERT(DATETIME,@DateEnd)";
				}
				else
				{
					text += " AND s.[Date]>=CONVERT(DATETIME,@DateStart) AND s.[Date]<=CONVERT(DATETIME,@DateEnd)";
				}
				this.selectCommand.Parameters.Add(new SqlParameter("@DateStart", SqlDbType.VarChar, 32));
				this.selectCommand.Parameters["@DateStart"].Value = sc.DateStart;
				this.selectCommand.Parameters.Add(new SqlParameter("@DateEnd", SqlDbType.VarChar, 32));
				this.selectCommand.Parameters["@DateEnd"].Value = sc.DateEnd;
			}
			if (sc.Remark != null)
			{
				if (text == "")
				{
					text += "s.Remark LIKE '%'+@Remark+'%'";
				}
				else
				{
					text += " AND s.Remark LIKE '%'+@Remark+'%'";
				}
				this.selectCommand.Parameters.Add(new SqlParameter("@Remark", SqlDbType.NVarChar, 64));
				this.selectCommand.Parameters["@Remark"].Value = sc.Remark.Replace(" ", "%");
			}
			if (text != "")
			{
				text = " WHERE " + text;
			}
			DataTable dataTable = new DataTable("Stuff");
			string commandText = "SELECT s.PKID AS 序号,s.[Type] AS 类型,s.Length as 长,s.Width AS 宽,s.Quantity AS 数量,convert(decimal(12, 2),(s.Length*s.Width*s.Quantity*1.0)/1000000) as 面积,s.[Date] AS 日期,s.Remark AS 备注,o.[Name] as 操作者 from [Stuff] as s inner join [Operator] as o on s.OperatorID=o.PKID" + text + " ORDER BY s.[Date]";
			try
			{
				this.selectCommand.Connection = new SqlConnection(AppSettings.ConnectionString);
				this.selectCommand.CommandText = commandText;
				this.selectCommand.Connection.Open();
				this.dataAdapter = new SqlDataAdapter(this.selectCommand);
				this.dataAdapter.Fill(dataTable);
			}
			catch (SqlException ex)
			{
				throw ex;
			}
			return dataTable;
		}

		public DataTable GetTypeSubtotalByCondition(StuffCondition sc)
		{
			this.selectCommand = new SqlCommand();
			string text = "";
			if (sc.Type != null)
			{
				if (text == "")
				{
					text += "s.[Type] LIKE '%'+@Type+'%'";
				}
				else
				{
					text += " AND s.[Type] LIKE '%'+@Type+'%'";
				}
				this.selectCommand.Parameters.Add(new SqlParameter("@Type", SqlDbType.NVarChar, 32));
				this.selectCommand.Parameters["@Type"].Value = sc.Type.Replace(" ", "%");
			}
			if (sc.Length > 0)
			{
				if (text == "")
				{
					text += "s.Length =@Length";
				}
				else
				{
					text += " AND s.Length =@Length";
				}
				this.selectCommand.Parameters.Add(new SqlParameter("@Length", SqlDbType.Int));
				this.selectCommand.Parameters["@Length"].Value = sc.Length;
			}
			if (sc.Width > 0)
			{
				if (text == "")
				{
					text += "s.Width =@Width";
				}
				else
				{
					text += " AND s.Width =@Width";
				}
				this.selectCommand.Parameters.Add(new SqlParameter("@Width", SqlDbType.Int));
				this.selectCommand.Parameters["@Width"].Value = sc.Width;
			}
			if (sc.Quantity > 0)
			{
				if (text == "")
				{
					text += "s.Quantity =@Quantity";
				}
				else
				{
					text += " AND s.Quantity =@Quantity";
				}
				this.selectCommand.Parameters.Add(new SqlParameter("@Quantity", SqlDbType.Int));
				this.selectCommand.Parameters["@Quantity"].Value = sc.Quantity;
			}
			if (sc.DateStart != null)
			{
				if (text == "")
				{
					text += "s.[Date]>=CONVERT(DATETIME,@DateStart) AND s.[Date]<=CONVERT(DATETIME,@DateEnd)";
				}
				else
				{
					text += " AND s.[Date]>=CONVERT(DATETIME,@DateStart) AND s.[Date]<=CONVERT(DATETIME,@DateEnd)";
				}
				this.selectCommand.Parameters.Add(new SqlParameter("@DateStart", SqlDbType.VarChar, 32));
				this.selectCommand.Parameters["@DateStart"].Value = sc.DateStart;
				this.selectCommand.Parameters.Add(new SqlParameter("@DateEnd", SqlDbType.VarChar, 32));
				this.selectCommand.Parameters["@DateEnd"].Value = sc.DateEnd;
			}
			if (sc.Remark != null)
			{
				if (text == "")
				{
					text += "s.Remark LIKE '%'+@Remark+'%'";
				}
				else
				{
					text += " AND s.Remark LIKE '%'+@Remark+'%'";
				}
				this.selectCommand.Parameters.Add(new SqlParameter("@Remark", SqlDbType.NVarChar, 64));
				this.selectCommand.Parameters["@Remark"].Value = sc.Remark.Replace(" ", "%");
			}
			if (text != "")
			{
				text = " WHERE " + text;
			}
			DataTable dataTable = new DataTable("TypeSubtotal");
			string commandText = "SELECT [Type] AS 类型,sum(Quantity) AS 数量,sum(convert(decimal(12, 2),(Length*Width*Quantity*1.0)/1000000)) as 面积 from [Stuff] as s" + text + " group by s.[Type]";
			try
			{
				this.selectCommand.Connection = new SqlConnection(AppSettings.ConnectionString);
				this.selectCommand.CommandText = commandText;
				this.selectCommand.Connection.Open();
				this.dataAdapter = new SqlDataAdapter(this.selectCommand);
				this.dataAdapter.Fill(dataTable);
			}
			catch (SqlException ex)
			{
				throw ex;
			}
			return dataTable;
		}

		public bool AddStuff(string sType, int nLength, int nWidth, int nQuantity, DateTime dtDate, string sRemark, int nOperatorID)
		{
			try
			{
				this.insertCommand = new SqlCommand("InsertStuff", new SqlConnection(AppSettings.ConnectionString));
				this.insertCommand.CommandType = CommandType.StoredProcedure;
				this.insertCommand.Connection.Open();
				this.insertCommand.Parameters.Add(new SqlParameter("@Type", SqlDbType.NVarChar, 32));
				this.insertCommand.Parameters.Add(new SqlParameter("@Length", SqlDbType.Int));
				this.insertCommand.Parameters.Add(new SqlParameter("@Width", SqlDbType.Int));
				this.insertCommand.Parameters.Add(new SqlParameter("@Quantity", SqlDbType.Int));
				this.insertCommand.Parameters.Add(new SqlParameter("@Date", SqlDbType.VarChar, 32));
				this.insertCommand.Parameters.Add(new SqlParameter("@Remark", SqlDbType.NVarChar, 64));
				this.insertCommand.Parameters.Add(new SqlParameter("@OperatorID", SqlDbType.Int));
				this.insertCommand.Parameters["@Type"].Value = sType;
				this.insertCommand.Parameters["@Length"].Value = nLength;
				this.insertCommand.Parameters["@Width"].Value = nWidth;
				this.insertCommand.Parameters["@Quantity"].Value = nQuantity;
				this.insertCommand.Parameters["@Date"].Value = dtDate.ToShortDateString();
				this.insertCommand.Parameters["@Remark"].Value = sRemark;
				this.insertCommand.Parameters["@OperatorID"].Value = nOperatorID;
				this.insertCommand.ExecuteNonQuery();
			}
			catch (SqlException ex)
			{
				throw ex;
			}
			return true;
		}

		public bool UpdateStuff(int nPKID, string sType, int nLength, int nWidth, int nQuantity, DateTime dtDate, string sRemark, int nOperatorID)
		{
			try
			{
				this.insertCommand = new SqlCommand("UpdateStuff", new SqlConnection(AppSettings.ConnectionString));
				this.insertCommand.CommandType = CommandType.StoredProcedure;
				this.insertCommand.Connection.Open();
				this.insertCommand.Parameters.Add(new SqlParameter("@PKID", SqlDbType.Int));
				this.insertCommand.Parameters.Add(new SqlParameter("@Type", SqlDbType.NVarChar, 32));
				this.insertCommand.Parameters.Add(new SqlParameter("@Length", SqlDbType.Int));
				this.insertCommand.Parameters.Add(new SqlParameter("@Width", SqlDbType.Int));
				this.insertCommand.Parameters.Add(new SqlParameter("@Quantity", SqlDbType.Int));
				this.insertCommand.Parameters.Add(new SqlParameter("@Date", SqlDbType.VarChar, 32));
				this.insertCommand.Parameters.Add(new SqlParameter("@Remark", SqlDbType.NVarChar, 64));
				this.insertCommand.Parameters.Add(new SqlParameter("@OperatorID", SqlDbType.Int));
				this.insertCommand.Parameters["@PKID"].Value = nPKID;
				this.insertCommand.Parameters["@Type"].Value = sType;
				this.insertCommand.Parameters["@Length"].Value = nLength;
				this.insertCommand.Parameters["@Width"].Value = nWidth;
				this.insertCommand.Parameters["@Quantity"].Value = nQuantity;
				this.insertCommand.Parameters["@Date"].Value = dtDate.ToShortDateString();
				this.insertCommand.Parameters["@Remark"].Value = sRemark;
				this.insertCommand.Parameters["@OperatorID"].Value = nOperatorID;
				this.insertCommand.ExecuteNonQuery();
			}
			catch (SqlException ex)
			{
				throw ex;
			}
			return true;
		}

		public void Dispose()
		{
			this.Dispose(true);
			GC.SuppressFinalize(true);
		}

		protected virtual void Dispose(bool disposing)
		{
			if (disposing)
			{
				if (this.dataAdapter != null)
				{
					if (this.dataAdapter.SelectCommand != null)
					{
						if (this.dataAdapter.SelectCommand.Connection != null)
						{
							this.dataAdapter.SelectCommand.Connection.Dispose();
							this.dataAdapter.SelectCommand.Connection = null;
						}
						this.dataAdapter.SelectCommand.Dispose();
						this.dataAdapter.SelectCommand = null;
					}
					this.dataAdapter.Dispose();
					this.dataAdapter = null;
				}
				if (this.insertCommand != null)
				{
					if (this.insertCommand.Connection != null)
					{
						this.insertCommand.Connection.Dispose();
						this.insertCommand.Connection = null;
					}
					this.insertCommand.Dispose();
					this.insertCommand = null;
				}
				if (this.updateCommand != null)
				{
					if (this.updateCommand.Connection != null)
					{
						this.updateCommand.Connection.Dispose();
						this.updateCommand.Connection = null;
					}
					this.updateCommand.Dispose();
					this.updateCommand = null;
				}
				if (this.deleteCommand != null)
				{
					if (this.deleteCommand.Connection != null)
					{
						this.deleteCommand.Connection.Dispose();
						this.deleteCommand.Connection = null;
					}
					this.deleteCommand.Dispose();
					this.deleteCommand = null;
				}
			}
		}
	}
}
